package com.carol.lmsbe.service.impl;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.carol.lmsbe.dto.StatisticsQueryRequest;
import com.carol.lmsbe.entity.*;
import com.carol.lmsbe.mapper.*;
import com.carol.lmsbe.service.ReportService;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.ByteArrayResource;
import org.springframework.core.io.Resource;
import org.springframework.stereotype.Service;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.List;

/**
 * 报表服务实现类
 */
@Service
public class ReportServiceImpl implements ReportService {

    @Autowired
    private EquipmentMapper equipmentMapper;

    @Autowired
    private LaboratoryMapper laboratoryMapper;

    @Autowired
    private ConsumablePlanMapper consumablePlanMapper;

    @Autowired
    private ConsumableReceiveMapper consumableReceiveMapper;

    @Autowired
    private ConsumableItemMapper consumableItemMapper;

    @Autowired
    private EquipmentRepairMapper equipmentRepairMapper;

    @Autowired
    private EquipmentScrapMapper equipmentScrapMapper;

    @Override
    public Resource exportEquipmentReport(StatisticsQueryRequest request) throws Exception {
        Workbook workbook = new XSSFWorkbook();
        
        // 创建设备统计表
        Sheet sheet = workbook.createSheet("设备统计");
        
        // 创建标题行
        Row headerRow = sheet.createRow(0);
        String[] headers = {"设备名称", "设备编号", "类别", "状态", "单价", "数量", "总价值", "实验室", "购买日期", "厂家", "负责人"};
        
        CellStyle headerStyle = workbook.createCellStyle();
        Font headerFont = workbook.createFont();
        headerFont.setBold(true);
        headerStyle.setFont(headerFont);
        headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        
        for (int i = 0; i < headers.length; i++) {
            Cell cell = headerRow.createCell(i);
            cell.setCellValue(headers[i]);
            cell.setCellStyle(headerStyle);
        }
        
        // 构建查询条件
        LambdaQueryWrapper<Equipment> queryWrapper = new LambdaQueryWrapper<>();
        if (request.getLaboratoryId() != null) {
            queryWrapper.eq(Equipment::getLaboratoryId, request.getLaboratoryId());
        }
        if (request.getCategory() != null && !request.getCategory().isEmpty()) {
            queryWrapper.eq(Equipment::getCategory, request.getCategory());
        }
        if (request.getStatus() != null && !request.getStatus().isEmpty()) {
            queryWrapper.eq(Equipment::getStatus, request.getStatus());
        }
        
        // 从数据库获取设备数据
        List<Equipment> equipmentList = equipmentMapper.selectList(queryWrapper);
        
        // 填充数据
        int rowNum = 1;
        for (Equipment equipment : equipmentList) {
            Row dataRow = sheet.createRow(rowNum++);
            
            // 获取实验室名称
            Laboratory laboratory = laboratoryMapper.selectById(equipment.getLaboratoryId());
            String laboratoryName = laboratory != null ? laboratory.getName() : "未知实验室";
            
            // 计算总价值
            BigDecimal totalValue = equipment.getUnitPrice().multiply(BigDecimal.valueOf(equipment.getQuantity()));
            
            dataRow.createCell(0).setCellValue(equipment.getName());
            dataRow.createCell(1).setCellValue(equipment.getCode());
            dataRow.createCell(2).setCellValue(equipment.getCategory());
            dataRow.createCell(3).setCellValue(equipment.getStatus());
            dataRow.createCell(4).setCellValue(equipment.getUnitPrice().doubleValue());
            dataRow.createCell(5).setCellValue(equipment.getQuantity());
            dataRow.createCell(6).setCellValue(totalValue.doubleValue());
            dataRow.createCell(7).setCellValue(laboratoryName);
            dataRow.createCell(8).setCellValue(equipment.getPurchaseDate() != null ? 
                equipment.getPurchaseDate().format(DateTimeFormatter.ofPattern("yyyy-MM-dd")) : "");
            dataRow.createCell(9).setCellValue(equipment.getManufacturer() != null ? equipment.getManufacturer() : "");
            dataRow.createCell(10).setCellValue(equipment.getHandlerName() != null ? equipment.getHandlerName() : "");
        }
        
        // 自动调整列宽
        for (int i = 0; i < headers.length; i++) {
            sheet.autoSizeColumn(i);
        }
        
        return createResourceFromWorkbook(workbook);
    }

    @Override
    public Resource exportConsumableReport(StatisticsQueryRequest request) throws Exception {
        Workbook workbook = new XSSFWorkbook();
        
        // 创建易耗品统计表
        Sheet sheet = workbook.createSheet("易耗品统计");
        
        // 创建标题行
        Row headerRow = sheet.createRow(0);
        String[] headers = {"物品名称", "规格", "单位", "计划数量", "实际数量", "单价", "计划金额", "实际金额", "实验室", "学期", "状态"};
        
        CellStyle headerStyle = workbook.createCellStyle();
        Font headerFont = workbook.createFont();
        headerFont.setBold(true);
        headerStyle.setFont(headerFont);
        headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        
        for (int i = 0; i < headers.length; i++) {
            Cell cell = headerRow.createCell(i);
            cell.setCellValue(headers[i]);
            cell.setCellStyle(headerStyle);
        }
        
        // 构建查询条件
        LambdaQueryWrapper<ConsumableItem> queryWrapper = new LambdaQueryWrapper<>();
        if (request.getLaboratoryId() != null) {
            // 通过计划关联实验室
            queryWrapper.exists("SELECT 1 FROM consumable_plan cp WHERE cp.id = consumable_item.plan_id AND cp.laboratory_id = " + request.getLaboratoryId());
        }
        if (request.getSemester() != null && !request.getSemester().isEmpty()) {
            // 通过计划关联学期
            queryWrapper.exists("SELECT 1 FROM consumable_plan cp WHERE cp.id = consumable_item.plan_id AND cp.semester = '" + request.getSemester() + "'");
        }
        
        // 从数据库获取易耗品数据
        List<ConsumableItem> itemList = consumableItemMapper.selectList(queryWrapper);
        
        // 填充数据
        int rowNum = 1;
        for (ConsumableItem item : itemList) {
            Row dataRow = sheet.createRow(rowNum++);
            
            // 获取计划信息
            ConsumablePlan plan = consumablePlanMapper.selectById(item.getPlanId());
            if (plan == null) continue;
            
            // 获取实验室名称
            Laboratory laboratory = laboratoryMapper.selectById(plan.getLaboratoryId());
            String laboratoryName = laboratory != null ? laboratory.getName() : "未知实验室";
            
            // 计算计划金额和实际金额
            BigDecimal plannedAmount = item.getUnitPrice().multiply(BigDecimal.valueOf(item.getPlannedQuantity()));
            BigDecimal actualAmount = item.getUnitPrice().multiply(BigDecimal.valueOf(item.getActualQuantity()));
            
            dataRow.createCell(0).setCellValue(item.getName());
            dataRow.createCell(1).setCellValue(item.getSpecification());
            dataRow.createCell(2).setCellValue(item.getUnit());
            dataRow.createCell(3).setCellValue(item.getPlannedQuantity());
            dataRow.createCell(4).setCellValue(item.getActualQuantity());
            dataRow.createCell(5).setCellValue(item.getUnitPrice().doubleValue());
            dataRow.createCell(6).setCellValue(plannedAmount.doubleValue());
            dataRow.createCell(7).setCellValue(actualAmount.doubleValue());
            dataRow.createCell(8).setCellValue(laboratoryName);
            dataRow.createCell(9).setCellValue(plan.getSemester());
            dataRow.createCell(10).setCellValue(plan.getStatus());
        }
        
        // 自动调整列宽
        for (int i = 0; i < headers.length; i++) {
            sheet.autoSizeColumn(i);
        }
        
        return createResourceFromWorkbook(workbook);
    }

    @Override
    public Resource exportEquipmentUsageReport(StatisticsQueryRequest request) throws Exception {
        Workbook workbook = new XSSFWorkbook();
        
        // 创建设备维修记录报表
        Sheet sheet = workbook.createSheet("设备维修记录");
        
        // 创建标题行
        Row headerRow = sheet.createRow(0);
        String[] headers = {"设备名称", "设备编号", "故障描述", "维修日期", "维修费用", "维修厂家", "负责人", "实验室", "维修状态"};
        
        CellStyle headerStyle = workbook.createCellStyle();
        Font headerFont = workbook.createFont();
        headerFont.setBold(true);
        headerStyle.setFont(headerFont);
        headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        
        for (int i = 0; i < headers.length; i++) {
            Cell cell = headerRow.createCell(i);
            cell.setCellValue(headers[i]);
            cell.setCellStyle(headerStyle);
        }
        
        // 从数据库获取维修记录数据
        LambdaQueryWrapper<EquipmentRepair> queryWrapper = new LambdaQueryWrapper<>();
        if (request.getStartDate() != null) {
            queryWrapper.ge(EquipmentRepair::getRepairDate, request.getStartDate());
        }
        if (request.getEndDate() != null) {
            queryWrapper.le(EquipmentRepair::getRepairDate, request.getEndDate());
        }
        
        List<EquipmentRepair> repairList = equipmentRepairMapper.selectList(queryWrapper);
        
        // 填充数据
        int rowNum = 1;
        for (EquipmentRepair repair : repairList) {
            Row dataRow = sheet.createRow(rowNum++);
            
            // 获取设备信息
            Equipment equipment = equipmentMapper.selectById(repair.getEquipmentId());
            String equipmentName = equipment != null ? equipment.getName() : "未知设备";
            String equipmentCode = equipment != null ? equipment.getCode() : "未知编号";
            
            // 获取实验室名称
            String laboratoryName = "未知实验室";
            if (equipment != null && equipment.getLaboratoryId() != null) {
                Laboratory laboratory = laboratoryMapper.selectById(equipment.getLaboratoryId());
                laboratoryName = laboratory != null ? laboratory.getName() : "未知实验室";
            }
            
            dataRow.createCell(0).setCellValue(equipmentName);
            dataRow.createCell(1).setCellValue(equipmentCode);
            dataRow.createCell(2).setCellValue(repair.getProblemDescription() != null ? repair.getProblemDescription() : "");
            dataRow.createCell(3).setCellValue(repair.getRepairDate() != null ? 
                repair.getRepairDate().format(DateTimeFormatter.ofPattern("yyyy-MM-dd")) : "");
            dataRow.createCell(4).setCellValue(repair.getRepairCost() != null ? repair.getRepairCost().doubleValue() : 0.0);
            dataRow.createCell(5).setCellValue(repair.getRepairCompany() != null ? repair.getRepairCompany() : "");
            dataRow.createCell(6).setCellValue(repair.getResponsiblePerson() != null ? repair.getResponsiblePerson() : "");
            dataRow.createCell(7).setCellValue(laboratoryName);
            dataRow.createCell(8).setCellValue(repair.getStatus() != null ? repair.getStatus() : "");
        }
        
        // 自动调整列宽
        for (int i = 0; i < headers.length; i++) {
            sheet.autoSizeColumn(i);
        }
        
        return createResourceFromWorkbook(workbook);
    }

    @Override
    public Resource exportEquipmentScrapReport(StatisticsQueryRequest request) throws Exception {
        Workbook workbook = new XSSFWorkbook();
        
        // 创建设备报废统计报表
        Sheet sheet = workbook.createSheet("设备报废统计");
        
        // 创建标题行
        Row headerRow = sheet.createRow(0);
        String[] headers = {"设备名称", "设备编号", "报废日期", "报废原因", "原价值", "残值", "处理方式", "负责人", "实验室"};
        
        CellStyle headerStyle = workbook.createCellStyle();
        Font headerFont = workbook.createFont();
        headerFont.setBold(true);
        headerStyle.setFont(headerFont);
        headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        
        for (int i = 0; i < headers.length; i++) {
            Cell cell = headerRow.createCell(i);
            cell.setCellValue(headers[i]);
            cell.setCellStyle(headerStyle);
        }
        
        // 从数据库获取报废记录数据
        LambdaQueryWrapper<EquipmentScrap> queryWrapper = new LambdaQueryWrapper<>();
        if (request.getStartDate() != null) {
            queryWrapper.ge(EquipmentScrap::getScrapDate, request.getStartDate());
        }
        if (request.getEndDate() != null) {
            queryWrapper.le(EquipmentScrap::getScrapDate, request.getEndDate());
        }
        
        List<EquipmentScrap> scrapList = equipmentScrapMapper.selectList(queryWrapper);
        
        // 填充数据
        int rowNum = 1;
        for (EquipmentScrap scrap : scrapList) {
            Row dataRow = sheet.createRow(rowNum++);
            
            // 获取设备信息
            Equipment equipment = equipmentMapper.selectById(scrap.getEquipmentId());
            String equipmentName = equipment != null ? equipment.getName() : "未知设备";
            String equipmentCode = equipment != null ? equipment.getCode() : "未知编号";
            
            // 获取实验室名称
            String laboratoryName = "未知实验室";
            if (equipment != null && equipment.getLaboratoryId() != null) {
                Laboratory laboratory = laboratoryMapper.selectById(equipment.getLaboratoryId());
                laboratoryName = laboratory != null ? laboratory.getName() : "未知实验室";
            }
            
            // 计算原价值（从设备单价和数量计算）
            BigDecimal originalValue = BigDecimal.ZERO;
            if (equipment != null && equipment.getUnitPrice() != null && equipment.getQuantity() != null) {
                originalValue = equipment.getUnitPrice().multiply(BigDecimal.valueOf(equipment.getQuantity()));
            }
            
            dataRow.createCell(0).setCellValue(equipmentName);
            dataRow.createCell(1).setCellValue(equipmentCode);
            dataRow.createCell(2).setCellValue(scrap.getScrapDate() != null ? 
                scrap.getScrapDate().format(DateTimeFormatter.ofPattern("yyyy-MM-dd")) : "");
            dataRow.createCell(3).setCellValue(scrap.getScrapReason() != null ? scrap.getScrapReason() : "");
            dataRow.createCell(4).setCellValue(originalValue.doubleValue());
            dataRow.createCell(5).setCellValue(scrap.getDisposalValue() != null ? scrap.getDisposalValue().doubleValue() : 0.0);
            dataRow.createCell(6).setCellValue(scrap.getScrapMethod() != null ? scrap.getScrapMethod() : "");
            dataRow.createCell(7).setCellValue(scrap.getResponsiblePerson() != null ? scrap.getResponsiblePerson() : "");
            dataRow.createCell(8).setCellValue(laboratoryName);
        }
        
        // 自动调整列宽
        for (int i = 0; i < headers.length; i++) {
            sheet.autoSizeColumn(i);
        }
        
        return createResourceFromWorkbook(workbook);
    }

    private Resource createResourceFromWorkbook(Workbook workbook) throws IOException {
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        workbook.write(outputStream);
        workbook.close();
        
        byte[] bytes = outputStream.toByteArray();
        return new ByteArrayResource(bytes);
    }
}
